What’s the problem?

The aim of the study is to assist a real estate company that has a niche in purchasing properties to rent out short-term as part of their business model specifically within New York City. The company has already figured out that 2-bedroom properties are the best for investment; however, they do not know which zipcodes are the best to invest in. A data product is requested to build in order to help the company understand which zipcodes would generate the most profit on short term rentals within New York City.

Dataset

For this purpose, publicly available data from Zillow and AirBnB are used:

1. Cost Data - Estimate of value for two-bedroom properties provided by Zillow
   http://insideairbnb.com/get-the-data.html

2. Revenue Data - AirBnB dataset with relevant short-term rental information
   https://www.zillow.com/research/data/

3. The Latest Cost Data – A historical estimated data of value for two-bedroom properties from 2008-01 to 2019-11, provided by Zillow
   https://www.zillow.com/research/data/

Assumptions

1. Short-term rental: A property that is rented anywhere between one evening up to one month is considered a short-term rental.
   
2. Weekly and monthly price: Weekly or monthly price of a property is a discount price for longer term stays. A property without a weekly or monthly price does not have discount price.
   
3. Tenants’ booking habit: We assume that in a month 60% of bookings are single day bookings, 30% are weekly bookings and 10% of bookings are made for a month.
   
4. Occupancy rate: The occupancy rate is assumed to be 75%.
   
5. Weather/Holiday has little or no impact on number of bookings.
   
6. The revenue obtained from a property remains equal to that charged by the previous host.
   
7. The company will put properties on rent throughout the year every day.
   
8. The investor will pay for the property in cash (i.e. no mortgage/interest rate will need to be accounted for).
   
9. The time value of money discount rate is 0% (i.e. $1 today is worth the same 100 years from now).
   
10. All properties and all square feet within each locale can be assumed to be homogeneous (i.e. a 1000 square foot property in a locale such as Bronx or Manhattan generates twice the revenue and costs twice as much as any other 500 square foot property within that same locale.)

All the percentages assumed above can be modified by user preference.

Cost Data

Data provides estimated historical median price for 2-bedroom homes in each zip code, captured between year 1996 and 2017 and spread monthly.

kableExtra::kable_styling(knitr::kable(head(zillow_subset), "html"),
                          bootstrap_options ="striped") %>% 
  kableExtra::scroll_box(width = "100%", height = "250px")

cost_missing_val <- sapply(zillow_subset, function(x) sum(length(which(is.na(x)))))  
kable(as.data.frame(cost_missing_val)) %>% 
  kableExtra::kable_styling(bootstrap_options = "striped") %>%
  kableExtra::scroll_box(width = "100%", height = "250px")

Revenue Data

Revenue data contains information including details about the properties including location, number of bedrooms, room types, price and other details for stay.

kable(head(airbnb))  %>% 
  kableExtra::kable_styling(bootstrap_options ="striped") %>% 
  kableExtra::scroll_box(width = "100%", height = "250px")

In order to simplify the analysis, we only select 15 relevant variables and filter 2-bedroom properties in NYC to create a subset for the following section.

rev_missing_val <- sapply(airbnb, function(x) sum(length(which(is.na(x)))))  
kable(as.data.frame(rev_missing_val)) %>% 
  kableExtra::kable_styling(bootstrap_options = "striped") %>%
  kableExtra::scroll_box(width = "100%", height = "250px")

#Quality Check
#1. missing value of zip code of NYC in zillow
#solution: new data source can be connected in the future

#2. Remove needless character and convert character to numeric
chr_to_num <- function(col_name) {
  col_name <- str_sub(col_name, 2, -4)
  col_name <- str_replace_all(col_name, ",", "")
  col_name <- as.numeric(col_name)
}

airbnb_subset$price <- chr_to_num(airbnb_subset$price)
airbnb_subset$weekly_price <- chr_to_num(airbnb_subset$weekly_price)
airbnb_subset$monthly_price <- chr_to_num(airbnb_subset$monthly_price)


#3. NAs in weekly price and monthly price
#solution: fill in the NAs with 7 and 31 times of daily price

missing_weekly <- which(is.na(airbnb_subset["weekly_price"]))
airbnb_subset$weekly_price[missing_weekly] <- airbnb_subset$price[missing_weekly]*7

missing_monthly <- which(is.na(airbnb_subset["monthly_price"]))
airbnb_subset$monthly_price[missing_monthly] <- airbnb_subset$price[missing_monthly]*30


#4. extreme values in price
#It can't be $9,999 or $0 a night.
#The reason for this could be a data input error of the host
airbnb_subset <- subset(airbnb_subset, price < 9000 & price != 0)


#5. zipcode: convert numeric to character
airbnb_subset$zipcode <- as.character(airbnb_subset$zipcode)

Time Unit

The two datasets have different units of time. In order to complete the analysis, we determine a common unit of time, which is month.

ARIMA Forecast

The revenue data is scraped in 2019-07, but the cost data only have historical house The revenue data is scraped in 2019-07, but the cost data only have historical house value from 1996-04 to 2017-06. Due to shortage of time, we assume that there is seasonality in the price and that values depend not only on previous values (Auto Regressive AR) but also on differences between previous values (Moving Average MA). So, we apply ARIMA model to predict the cost of the properties in zipcodes from 2017-07 to 2019-07. We then attach the price of property in 2019-07 calculated at zipcode level with each zipcode as a new column to cost data.

#determine a common unit of time - monthly
airbnb_subset$last_scraped <- format(airbnb_subset$last_scraped,format="%Y-%m")


#trend of house price
design.mat <- cbind(1,1:121)
response.mat <- t(zillow_subset[,3:257])
response.mat <- response.mat[complete.cases(response.mat),]

reg <- lm.fit(design.mat, response.mat)$coefficients
zillow_subset <- cbind(zillow_subset, t(reg))
names(zillow_subset)[names(zillow_subset)=="x1"]="intercept"
names(zillow_subset)[names(zillow_subset)=="x2"]="slope"


#forecast
scraped_date <- unique(airbnb_subset$last_scraped)

zillow_subset$scraped_date <- NULL
for(i in 1:nrow(zillow_subset)){
  # Convert the monthly cost data into time series data 
  time_series = ts(as.vector(t(zillow_subset[,c(3:257)])[,i]),
                   start = c(1996,4),frequency = 12) 
  
  # Define ARIMA model to be used for prediction
  ARIMAfit = arima(time_series, order=c(1,1,1), seasonal=list(order=c(1,0,1),
                   period=NA), method="ML")
  
  # use the ARIMA model to predict the price from 2017-07 to 2019-07
  pred = predict(ARIMAfit, n.ahead = 25)
  
  # Store the predicted values in a variable
  pred_val <- pred$pred  
  
  # set the value of current price for the specific zipcode as price in 2019-07
  zillow_subset$scraped_date[i] <- pred_val[length(pred_val)] 
}

Data Join

In order to make sure this product is always applicable whenever new data is available or whenever we are ready to approach a new market, a function is built to link the data together in a scalable way by matching the scraped date and zipcode of two datasets.

After inner join, we recognize that there are 1508 properties of 24 zipcodes in 4 distinct neighborhoods - Manhattan, Queens, Brooklyn and Staten Island – matching together.

#link the data together in a scalable way
names(zillow_subset)[names(zillow_subset)=="RegionName"]="zipcode"
dt_full <- merge(airbnb_subset, zillow_subset, by = "zipcode")
dt <- select(dt_full, zipcode:RegionID, scraped_date, growth_rate = "slope")

Price corrected

Property type issue

If the property type == Private Room, the price of the property should be corrected by price*bedrooms

Average daily price

In order to calculate the average daily price, we need to consider both tenant booking habit and host requirement for minimum and maximum nights.

Based on the common sense of general booking habit, we assume that in a month 60% of bookings are single day bookings, 30% are weekly bookings and 10% of bookings are made for a month.

As for the minimum/maximum amount of nights the host is willing to rent out the property, we assume that the minimum/maximum nights determine whether a property can be directly booked for a weekly and for a month.

Considering these two factors, we can calculate the average daily price by a conditional metric of minimum/maximum nights and booking habit weights.

#Price corrected
#If the property type == Private Room, total price should be corrected by price*bedrooms
dt <- dt %>% 
  mutate(price = if_else(room_type == "Private room", price * 2, price)) 

#assumption: 0.6 daily price, 0.3 weekly price, 0.1 monthly price. 
#This coefficient can change
daily_prob <- 0.6
weekly_prob <- 0.3
monthly_prob <- 0.1
dt$avg_daily_price <- 0
for(i in 1:nrow(dt)){
  if(dt$minimum_nights[i]<7 & dt$maximum_nights[i]<7){
    dt$avg_daily_price[i] = dt$price[i]
  } else if (dt$minimum_nights[i]<7 & dt$maximum_nights[i]>=7 & dt$maximum_nights[i]<30){
    dt$avg_daily_price[i] = daily_prob*dt$price[i]
    +(weekly_prob+monthly_prob)*dt$weekly_price[i]/7
  } else if (dt$minimum_nights[i]<7 & dt$maximum_nights[i]>=30){
    dt$avg_daily_price[i] = daily_prob*dt$price[i]+weekly_prob*dt$weekly_price[i]/7+
      monthly_prob*dt$monthly_price[i]/30
  } else if (dt$minimum_nights[i]>=7 & dt$minimum_nights[i]<30
             & dt$maximum_nights[i]>=7 & dt$maximum_nights[i]<30){
    dt$avg_daily_price[i] = dt$weekly_price[i]/7
  } else if (dt$minimum_nights[i]>=7 & dt$minimum_nights[i]<30
             & dt$maximum_nights[i]>=30){
    dt$avg_daily_price[i] = (daily_prob+weekly_prob)*dt$weekly_price[i]/7
    +monthly_prob*dt$monthly_price[i]/30
  } else if (dt$minimum_nights[i]>=30 & dt$maximum_nights[i]>=30){
    dt$avg_daily_price[i] = dt$monthly_price[i]/30
  }
}

occupancy_rate <- 0.75
dt$monthly_rev <- dt$avg_daily_price*30*occupancy_rate
dt$annual_rev <- dt$monthly_rev*12


#summarize by zipcode
zip_summary <- dt %>%
  group_by(zipcode) %>%
  summarize(count = n(), 
            avg_monthly_rev = round(mean(monthly_rev),2), 
            avg_annual_rev = round(mean(annual_rev),2),
            avg_cost = round(mean(scraped_date),2),
            payback_year = round(mean(avg_cost/avg_annual_rev),2),
            rev_cost_ratio = round(avg_annual_rev/avg_cost,2),
            trend = round(mean(growth_rate),2),
            neighbourhood_group_cleansed = 
              paste(unique(neighbourhood_group_cleansed), collapse = ","))

#some zipcodes belong to more than one neighborhood
zip_summary <- zip_summary %>%
  separate(neighbourhood_group_cleansed, c("neighbourhood", NA), sep = ",")

Number of properties

Higher the number of properties, more choices our client can invest, and more rental activities can possibly have in the area.

At the neighborhood level, Manhattan and Brooklyn host highest number of properties. Queens and Staten Island have much fewer number of properties.

Zipcodes 11215, 10036, 10003, 11217, 10025, 10013 have more than 100 properties in each area, which makes them the top 10 based on volume of properties.

#number of properties
num_house_plot <- 
  zip_summary %>%
  arrange(desc(count)) %>%
  top_n(10, count) %>%
  ggplot(aes(x = reorder(zipcode, count), y = count, fill = neighbourhood))+
  geom_col()+
  coord_flip()+
  labs(x = "Zipcode", y = "Number of Properties")+
  theme_bw() + 
  theme(plot.background = element_blank(), panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),panel.border = element_blank(),
        axis.text.x = element_text(angle = 90, hjust = 1))+
  guides(fill = guide_legend(title = "Neighbourhood"))

ggplotly(num_house_plot)

Cost of properties

One of the primary constraints in decision of investment is the cost. Lower the cost, company can save a lot more and reach ROI faster.
Zipcodes in Manhattan walk away with highest property cost, with an average of about $2M. Brooklyn comes second, with about $1M. Zipcodes in Staten Island and Queens have much lower prices, with lower than $0.5M.

cost_plot <- 
  zip_summary %>%
  arrange(desc(avg_cost)) %>%
  top_n(24, avg_cost) %>%
  ggplot(aes(x = reorder(zipcode, avg_cost), y = avg_cost, fill = neighbourhood))+
  geom_col()+
  coord_flip()+
  labs(x = "Zipcode", y = "Cost of Properties")+
  theme_bw() + 
  theme(plot.background = element_blank(), panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),panel.border = element_blank(),
        axis.text.x = element_text(hjust = 1))+
  scale_y_continuous(breaks=c(0, 1000000, 2000000), labels=c("0","1M","2M"))+
  guides(fill = guide_legend(title = "Neighbourhood"))

ggplotly(cost_plot)

Average daily price

Higher the average daily rental price, faster is the cash flow. Therefore, company would look forward to making quick bucks with high daily price.
Manhattan has a wider spread with higher price ranging to $50-$1000 per night.
Brooklyn comes second also with wide spread but lower price comparing to Manhattan.
Staten Island and Queens have lower price and narrower distribution, and this is probably because of limited sample size.

price_plot <- ggplot(dt,aes(x = reorder(zipcode, avg_daily_price),
                            y = avg_daily_price, 
                            fill = neighbourhood_group_cleansed))+
  geom_boxplot()+
  coord_flip()+
  labs(x = "Zipcode", y = "Annual Daily Price of Properties")+
  theme_bw() + 
  theme(plot.background = element_blank(), panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),panel.border = element_blank(),
        axis.text.x = element_text(hjust = 1))+
  guides(fill = guide_legend(title = "Neighbourhood")) +
  scale_y_continuous(limits = quantile(dt$avg_daily_price, c(0, 0.99)))

ggplotly(price_plot)

Annual revenue

Different from the daily price, the annual revenue also taken into account the effects of weekly/monthly discount prices, minimum/maximum days and occupancy rate. Therefore, the annual revenue will provide a more comprehensive information to help us make better investment choices.
As usual, Manhattan has the highest revenue, and Brooklyn comes second.

rev_plot <- 
  zip_summary %>%
  arrange(desc(avg_annual_rev)) %>%
  top_n(10, avg_annual_rev) %>%
  ggplot(aes(x = reorder(zipcode, avg_annual_rev), 
             y = avg_annual_rev, fill = neighbourhood))+
  geom_col()+
  coord_flip()+
  labs(x = "Zipcode", y = "Annual Revenue of Properties")+
  theme_bw() + 
  theme(plot.background = element_blank(), panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),panel.border = element_blank(),
        axis.text.x = element_text(hjust = 1))+
  scale_y_continuous(breaks=c(0, 100000, 200000,300000), 
                     labels=c("0","1M","2M","3M"))+
  guides(fill = guide_legend(title = "Neighbourhood"))

ggplotly(rev_plot)

Payback year

Companies also value payback year when making investment decisions. The shorter the payback time, the faster the company can recoup its initial capital.
The zipcode 10306 in Staten Island has the shortest payback year (12 years), while nearly all the zipcodes in Manhattan and Brooklyn have more than 20 years of payback time.

#payback year
payback_plot <- 
  zip_summary %>%
  arrange(desc(payback_year)) %>%
  top_n(10, payback_year) %>%
  ggplot(aes(x = reorder(zipcode, payback_year),y = payback_year, fill = neighbourhood))+
  geom_col()+
  coord_flip()+
  labs(x = "Zipcode", y = "Payback Year of Properties")+
  theme_bw() + 
  theme(plot.background = element_blank(), panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),panel.border = element_blank(),
        axis.text.x = element_text(hjust = 1))+
  guides(fill = guide_legend(title = "Neighbourhood"))

ggplotly(payback_plot)

Revenue cost ratio

Company should invest in those zipcodes which provide high return, which also means high revenue to cost ratio.
We notice that zipcode 11434, 10308 and 10306 in Queens and Staten Island provide the highest return for each dollar spent in buying the property, which is about 8%. Then comes to 11234 in Brooklyn. Most of Manhattan’s zipcodes have low return rates.

#profitability
profit_plot <- 
  zip_summary %>%
  arrange(desc(rev_cost_ratio)) %>%
  top_n(10, rev_cost_ratio) %>%
  ggplot(aes(x = reorder(zipcode,rev_cost_ratio),
             y = rev_cost_ratio, fill = neighbourhood))+
  geom_col()+
  coord_flip()+
  labs(x = "Zipcode", y = "Profitability of Properties")+
  theme_bw() + 
  theme(plot.background = element_blank(), panel.grid.major = element_blank(),
        panel.grid.minor = element_blank(),panel.border = element_blank(),
        axis.text.x = element_text(hjust = 1))+
  guides(fill = guide_legend(title = "Neighbourhood"))

ggplotly(profit_plot)

Price Trend

When making investment decisions, companies should not only consider the current market situation, but should also have a developing vision. Price trend in the real estate industry is also important factors to consider.
From the time series plot, we can see that after 2010, prices in four neighborhoods are all increasing. Staten Island and Queens have a slightly higher increasing rate.

#growth rate
#summarize by neighborhood
    neighbor_summary <- dt_full %>%
      group_by(neighbourhood_group_cleansed) %>%
      summarize_each(funs(mean)) %>%
      select(neighbourhood_group_cleansed, contains("-"))
    
    
    t_neighbor_summary <- data.frame(t(neighbor_summary))
    t_neighbor_summary <-t_neighbor_summary[-1,] 
    names(t_neighbor_summary)[1:4]=c("Brooklyn", "Manhattan", "Queens", "Staten Island")
    t_neighbor_summary$date_ts <- seq(as.Date("1996-04-01"), as.Date("2017-06-01"),by = "month")
    t_neighbor_summary$Brooklyn <- as.numeric(paste(t_neighbor_summary$Brooklyn))
    t_neighbor_summary$Manhattan <- as.numeric(paste(t_neighbor_summary$Manhattan))
    t_neighbor_summary$Queens <- as.numeric(paste(t_neighbor_summary$Queens))
    t_neighbor_summary$`Staten Island` <- as.numeric(paste(t_neighbor_summary$`Staten Island`))
    
    
    trend_plot <- plot_ly(x = t_neighbor_summary$date_ts, y = t_neighbor_summary$Brooklyn, 
                          type="scatter", mode="lines", name = 'Brooklyn', fill = "Paired") %>%
      add_trace(p, x = t_neighbor_summary$date_ts, y = t_neighbor_summary$Manhattan, 
                type="scatter", mode="lines", name = 'Manhattan', fill = "Paired") %>%
      add_trace(p, x = t_neighbor_summary$date_ts, y = t_neighbor_summary$Queens, 
                type="scatter", mode="lines", name = 'Queens', fill = "Paired") %>%
      add_trace(p, x = t_neighbor_summary$date_ts, y = t_neighbor_summary$`Staten Island`, 
                type="scatter", mode="lines", name = 'Staten Island', fill = "Paired")
    
    
    ggplotly(trend_plot)

Comprehensive consideration:

• Based on the comprehensive scoring method, the top 10 zipcodes with the highest score are: 11434, 10306, 11215, 10036, 10303, 10304, 10308, 10305, 10314, 11234. Six of them are located in Staten Island, two are in Brooklyn, one in Manhattan and one in Queens.

Different key factors consideration:

Number of properties & Cost of properties

• Zipcodes 11215, 10303, 11434, 10304 and 10306 have substantial number of properties at low cost, which are located in Brooklyn, Staten Island and Queens. So, if the company has budget constraints then they should invest in buying properties in these zipcodes.

  • Weight of count = 0.5, weight of cost = 0.5

Annual revenue & Revenue cost ratio

• If the company is willing to buy properties having high cost, then they should invest in Zipcodes 10038 in Staten Island, 11215 in Brooklyn, 10036, 10003 and 10025 in Manhattan because these zipcodes not only have high number of costly properties but also provide very high return as the revenue is high.

  • Weight of count = 0.5, weight of revenue = 0.2, weight of profit = 0.5

Payback year

• If the company is more focused on getting its money back quickly with a limited initial investment, zipcodes 10038, 10314, 11434, 10303 and 10306 fit the bill, which are all located in Staten Island and Queens.

  • Weight of cost = 0.5, weight of payback year = 0.5

Price trend

• If the company attaches importance on long-term growth and future earnings and wants to invest in properties where rents are rising faster, these are the zipcodes to go: 10013, 10014, 10011 in Manhattan, 10308 in Staten Island, and 11201 in Brooklyn.

  • Weight of profit = 0.5, weight of trend = 0.5

Other thoughts:

• Our basic recommendation for the company is to diversify and buy properties in top performing zips of different neighborhoods with prime focus on Staten Island.

• Through our products, the company can adjust the weight of each factor according to its actual needs to obtain the most suitable investment portfolio plan for its own investment strategy.

• If the company is a risk taker, it can choose the property with high investment and high yield. If the company is risk averse, it can choose the property with low initial cost and fast return.

• Company should diversify its investment portfolios and locations to not only minimize investment risk, but also lay the foundation for future expansion in multiple markets.